
import pandas as pd

def compare_excel_files(file1, file2):
    # 读取两个Excel文件
    df1 = pd.read_excel(file1)
    df2 = pd.read_excel(file2)
    
    # 合并两个DataFrame，找出series_no相同但product_no不同的行
    merged = pd.merge(df1, df2, on='series_no', how='inner', suffixes=('_file1', '_file2'))
    diff_rows = merged[merged['product_no_file1'] != merged['product_no_file2']]
    
    return diff_rows

if __name__ == "__main__":
    file1 = "/opt/01-apache/python/data/bom1.xlsx"  # 替换为第一个Excel文件路径
    file2 = "/opt/01-apache/python/data/bom2.xlsx"  # 替换为第二个Excel文件路径
    
    result = compare_excel_files(file1, file2)
    
    # 输出结果
    print("找到差异行：")
    print(result)
    
    # 可选：将结果保存到新Excel文件
    result.to_excel("/opt/01-apache/python/data/differences.xlsx", index=True)
    
